clear all
set more off
set maxvar 20000

*Define directory with input data
cd "Q:"

*Load merged data
use se_analysis_data_cleaned_1996_2018, clear

*Footnote 3 - respondents in SER, but not DER
replace ser_se_flag_lcy=0 if ser_se_flag_lcy==. & tot_se_earn_lcy!=.
replace ser_ws_flag_lcy=0 if ser_ws_flag_lcy==. & non_se_earn_lcy!=.

gen ser_any_flag_lcy=0
replace ser_any_flag_lcy=1 if ser_se_flag_lcy==1 | ser_ws_flag_lcy==1

tab ser_any_flag_lcy if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & noimpute==1 & ssatopcoded_lcy==0 

*Footnote 10 - EITC eligibility - examine for 2016 only
egen income_hh=rowtotal(income income_s)
gen eitc_eligible=0
replace eitc_eligible=. if numresidentchildren==. | married==. | income_hh==. | age==. 
replace eitc_eligible=1 if numresidentchildren==0 & married==0 & income_hh>0 & income_hh<14.880 & age<65 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==0 & married==1 & income_hh>0 & income_hh<20.430 & age<65 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==1 & married==0 & income_hh>0 & income_hh<39.296 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==1 & married==1 & income_hh>0 & income_hh<44.846 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==2 & married==0 & income_hh>0 & income_hh<44.648 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==2 & married==1 & income_hh>0 & income_hh<50.198 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==3 & married==0 & income_hh>0 & income_hh<47.955 & investment_income<3.4
replace eitc_eligible=1 if numresidentchildren==3 & married==1 & income_hh>0 & income_hh<53.505 & investment_income<3.4

tab eitc_eligible if waveyr==2016 & insamp==1 & wtcrnh>0 & wtcrnh!=. & (merge_ser_lcy==3 | merge_der_lcy==3) & sswgts_ev_scaled!=.

*Table 1
local table1_hrs_int = "se_inc2_thou_real_noimp se_inc2_thou_real_noimp_cond wage_inc2_thou_real_noimp wage_inc2_thou_real_noimp_cond totinc_hrs_noimp totinc_hrs_noimp_cond"
local table1_ssa_int = "tot_se_earn_lcy_thou_real tot_se_earn_lcy_thou_real_cond non_se_earn_lcy_thou_real non_se_earn_lcy_thou_real_cond totinc_ssa totinc_ssa_cond"
local table1_hrs = "any_hrs_se any_hrs_e hrs_se_or_e"
local table1_ssa = "any_ssa_se_lcy any_ssa_e_lcy ssa_se_or_e_lcy"

putexcel set "Q:\U\jabramow\CenHRS\RSF Proposal\HRS Data and Code\Longitudinal\table1", replace
putexcel B2=("Mean 1") C2=("Var 1") D2=("N")
local i = 3

*Weighted
svyset secu [pw=wtcrnh], strata(stratum) singleunit(scaled)

foreach v in `table1_hrs' {
	svy: mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

foreach v in `table1_hrs_int' {
	svy: mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & noimpute==1 & ssatopcoded_lcy==0 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}


svyset secu [pw=sswgts_ev_scaled], strata(stratum) singleunit(scaled)

foreach v in `table1_hrs' `table1_ssa' {
	svy: mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & (merge_ser_lcy==3 | merge_der_lcy==3) & sswgts_ev_scaled!=. 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

foreach v in `table1_hrs_int' `table1_ssa_int' {
	svy: mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & (merge_ser_lcy==3 | merge_der_lcy==3) & sswgts_ev_scaled!=. & noimpute==1 & ssatopcoded_lcy==0 & (totinc_ssa!=. & totinc_hrs_noimp!=.)
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

*Unweighted
foreach v in `table1_hrs' {
	mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=.
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

foreach v in `table1_hrs_int' {
	mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & noimpute==1 & ssatopcoded_lcy==0
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

foreach v in `table1_hrs' `table1_ssa' {
	mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & (merge_ser_lcy==3 | merge_der_lcy==3) & sswgts_ev_scaled!=.
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

foreach v in `table1_hrs_int' `table1_ssa_int'{
	mean `v' if insamp==1 & waveyr>=2004 & waveyr<=2016 & wtcrnh>0 & wtcrnh!=. & (merge_ser_lcy==3 | merge_der_lcy==3) & sswgts_ev_scaled!=. & noimpute==1 & ssatopcoded_lcy==0 & (totinc_ssa!=. & totinc_hrs_noimp!=.)
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

*Table for Appendix 4

putexcel set "Q:\U\jabramow\CenHRS\RSF Proposal\HRS Data and Code\Longitudinal\appendix4", replace
putexcel B2=("Num 1") C2=("Var 1") D2=("N")
local i = 3

foreach v of varlist statscats_* {
	total `v' if insamp==1 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) C`i'=v[1,1] D`i'=matrix(e(_N))
	local i = `i'+1
}

*Table 2
svy: tab hrs_type ssa_type if insamp==1 

*Figure for Appendix 5
svy: proportion any_ssa_se_lcy if insamp==1 & (any_ssa_se_lcy==1 | any_ssa_e_lcy==1) & iwyear<=2012, over(age_gp_abr)
svy: proportion any_hrs_se if insamp==1 & (any_hrs_se==1 | any_hrs_e==1) & iwyear<=2012, over(age_gp_abr)

*Table 3 
local tablevars = "age male whitenh blacknh hispanic foreignborn educyr married residentchildrendum hrspw_main hrspw sayspartlyretired saysfullyretired proxy nonfinresp yrssinceenter"

putexcel set "Q:\U\jabramow\CenHRS\RSF Proposal\HRS Data and Code\Longitudinal\table3", replace
putexcel B2=("Mean 1") C2=("Mean 2") D2=("Mean 3") E2=("Mean 4") F2=("Var 1") G2=("Var 2") H2=("Var 3") I2=("Var 4") J2=("N")
local i = 3

foreach v in `tablevars' {
	svy: mean `v' if insamp==1 & matchcat!=. & (any_ssa_se_lcy==1 | any_hrs_se==1), over(matchcat) 
	*svy: mean `v' if insamp==1 & matchcat!=., over(matchcat) 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) F`i'=v[1,1] G`i'=v[2,2] H`i'=v[3,3] I`i'=v[4,4] J`i'=matrix(e(_N))
	local i = `i'+1
}

*Table 4 - Roles

local role = "role_char_0 role_char_1 role_char_5 role_char_4"

putexcel set "Q:\U\jabramow\CenHRS\RSF Proposal\HRS Data and Code\Longitudinal\table4", replace
putexcel B2=("Mean 1") C2=("Mean 2") D2=("Mean 3") E2=("Mean 4") F2=("Mean 5") G2=("Mean 6") H2=("Mean 7") I2=("Mean 8") J2=("Mean 9") K2=("Mean 10") L2=("Mean 11") M2=("Mean 12") N2=("Mean 13") O2=("Mean 14") P2=("Mean 15") Q2=("Mean 16") ///
		 R2=("Var 1") S2=("Var 2") T2=("Var 3") U2=("Var 4") V2=("Var 5") W2=("Var 6") X2=("Var 7") Y2=("Var 8") Z2=("Var 9") AA2=("Var 10") AB2=("Var 11") AC2=("Var 12") AD2=("Var 13") AE2=("Var 14") AF2=("Var 15") AG2=("Var 16") AH2=("N")
local i = 3

foreach v in `role' {
	svy: tab roletype3 if insamp==1 & `v'==1 
	matrix v=e(V)
	putexcel A`i'=("`v'") B`i'=matrix(e(b)) R`i'=v[1,1] S`i'=v[2,2] T`i'=v[3,3] U`i'=v[4,4] V`i'=v[5,5] W`i'=v[6,6] X`i'=v[7,7] Y`i'=v[8,8] Z`i'=v[9,9] AA`i'=v[10,10] AB`i'=v[11,11] AC`i'=v[12,12] AD`i'=v[13,13] AE`i'=v[14,14] AF`i'=v[15,15] AG`i'=v[16,16] 
	local i = `i'+1
}